package com.ruoyi.factory.matecount.service.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.factory.bom.domain.BomData;
import com.ruoyi.factory.common.utils.IdGen;
import com.ruoyi.factory.matecount.domain.MaterialBom;
import com.ruoyi.factory.matecount.mapper.MaterialBomMapper;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import com.ruoyi.factory.matecount.mapper.MaterialCountMapper;
import com.ruoyi.factory.matecount.domain.MaterialCount;
import com.ruoyi.factory.matecount.service.IMaterialCountService;

/**
 * 物料销售数据Service业务层处理
 * 
 * @author ljj
 * @date 2024-03-06
 */
@Service
public class MaterialCountServiceImpl implements IMaterialCountService 
{
    @Autowired
    private MaterialCountMapper materialCountMapper;

    @Autowired
    private MaterialBomMapper materialBomMapper;

    @Value("${bomdata.sqlServer.sqlServerUrl}")
    private String sqlserverurl;

    @Value("${bomdata.sqlServer.userName}")
    private String sqlserverusername;

    @Value("${bomdata.sqlServer.passWord}")
    private String sqlserverpassword;

    private Connection conn;

    private PreparedStatement sql;

    private ResultSet rs;

    private Map<String,String> param = new HashMap<>();

    /**
     * 查询物料销售数据
     * 
     * @param id 物料销售数据主键
     * @return 物料销售数据
     */
    @Override
    public MaterialCount selectMaterialCountById(String id)
    {
        return materialCountMapper.selectMaterialCountById(id);
    }

    /**
     * 查询物料销售数据列表
     * 
     * @param materialCount 物料销售数据
     * @return 物料销售数据
     */
    @Override
    public List<MaterialCount> selectMaterialCountList(MaterialCount materialCount)
    {
        //查询当月判断erp中条数是多少,条数有变化则重新刷新数据
//        Date periodDate = materialCount.getPeriodDate();
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
//        String format = sdf.format(periodDate);
////        String pk_org = param.get("pk_org");
////        String pk_org = "";
////        if(StringUtils.isEmpty(pk_org)){
////            pk_org = "0001A110000000002TJA";
////        }
//        int i = materialCountMapper.lookForMaterialc(format);
//        String resulti = param.get("resulti");
//        if (!String.valueOf(i).equals(resulti)){
//            //刷新数据
//            JSONObject psj = new JSONObject();
//            psj.put("date",format);
////            psj.put("pkOrg",pk_org);
//            updatame(psj);
//            param.put("resulti",resulti);
//        }
//        startPage();
        return materialCountMapper.selectMaterialCountList2(materialCount);
    }

    @Override
    public List<MaterialCount> selectMaterialCountRootList(MaterialCount materialCount)
    {
        return materialCountMapper.selectMaterialCountList(materialCount);
    }

    /**
     * 新增物料销售数据
     * 
     * @param materialCount 物料销售数据
     * @return 结果
     */
    @Override
    public int insertMaterialCount(MaterialCount materialCount)
    {
        materialCount.setCreateTime(DateUtils.getNowDate());
        return materialCountMapper.insertMaterialCount(materialCount);
    }

    /**
     * 修改物料销售数据
     * 
     * @param materialCount 物料销售数据
     * @return 结果
     */
    @Override
    public int updateMaterialCount(MaterialCount materialCount)
    {
        materialCount.setUpdateTime(DateUtils.getNowDate());
        return materialCountMapper.updateMaterialCount(materialCount);
    }

    /**
     * 批量删除物料销售数据
     * 
     * @param ids 需要删除的物料销售数据主键
     * @return 结果
     */
    @Override
    public int deleteMaterialCountByIds(String[] ids)
    {
        return materialCountMapper.deleteMaterialCountByIds(ids);
    }

    /**
     * 删除物料销售数据信息
     * 
     * @param id 物料销售数据主键
     * @return 结果
     */
    @Override
    public int deleteMaterialCountById(String id)
    {
        return materialCountMapper.deleteMaterialCountById(id);
    }

    /**
     * @author: Liujiaji
     * @date: 2024/3/7 09:01
     * @description: TODO 更新erp数据到中间服务数据库
     * @param: [type]
     * @return: void
     **/
    @Override
    public void updatame(JSONObject req)
    {
        // 1.查询上个月的销售发票数据
        String date = req.optString("date");
        Calendar instance = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        if(StringUtils.isEmpty(date)){
            //定时任务每月最后一天执行
            date = sdf.format(new Date());
        }
//        String pk_org = req.optString("pkOrg");
//        param.put("pk_org",pk_org);

        try {
            Date parse = sdf.parse(date);
            instance.setTime(parse);
            materialCountMapper.deleteMaterialCountByDate(parse);
            materialBomMapper.deleteMaterialBomByDate(parse);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //筛选掉内部交易
        List<MaterialCount> materialCounts = materialCountMapper.lookForMaterial(date);
        // 查询中间服务表中是否存在 存在则将数量修改 查询信息为物料+时间
        List<MaterialCount> list = new ArrayList<>();
        for (MaterialCount materialCount : materialCounts) {
            if("100103000000002".equals(materialCount.getPartId())){
                System.out.println("cg");
            }
            //判断名称是否带有进料加工 有则根据规格查询不带物料加工的
            MaterialCount remate = distggmeta(materialCount);
            if (null != remate){
                materialCount.setPartId(remate.getPartId());
                materialCount.setErpName(remate.getErpName());
            }
            //1.查询信息 根据物料编码和期间查询
            MaterialCount pmaterialCount = new MaterialCount();
            pmaterialCount.setPartId(materialCount.getPartId());
//            pmaterialCount.setPeriodDate(materialCount.getPeriodDate());
            pmaterialCount.setYear(String.valueOf(instance.get(Calendar.YEAR)));
            pmaterialCount.setMonth(String.valueOf(instance.get(Calendar.MONTH)+1));
            List<MaterialCount> reslist = materialCountMapper.selectMaterialCountList(pmaterialCount);
            // 2.遍历明细 查询bom表匹配之后存到中间服务
            //为空则复制参数插入 不为空则计数插入
            if(reslist.isEmpty()){
               //查询和插入 复合剂
                BomData bc = new BomData();
                bc.setPartid(materialCount.getPartId());
//                bc.setChinaname(materialCount.getChinaName());
//                List<BomCost> bomCosts = bomCostMapper.selectBomCostList(bc);
                List<BomData> bomDatas = selectBomDataListOrder(bc);

                if(!bomDatas.isEmpty()){
                    //不为空说明有信息 是什么物料根据层级和父原料判断 可能为单剂
                    BomData bomData = bomDatas.get(0);
                    for (BomData data : bomDatas) {
                        String parentId = data.getAssemblelevel();
                        if(materialCount.getMateClassCode().length()<6){
                            //其他
                            materialCount.setMateType("2");
                        }else{
                            String mateClassCode = materialCount.getMateClassCode().substring(0, 6);
                            if (mateClassCode.equals("100202")){
                                //复合剂
                                materialCount.setMateType("0");
                                if("1".equals(parentId)){
                                    bomData=data;
                                }
                            }else if(mateClassCode.equals("100201")){
                                //单剂
                                materialCount.setMateType("1");
                                if("2".equals(parentId)){
                                    bomData=data;
                                }
                            }else{
                                //其他
                                materialCount.setMateType("2");
                            }
                        }
                    }
                    //整理erp物料和bom信息
                    MaterialCount toconver = toconver(bomData, materialCount);
                    toconver.setYear(String.valueOf(instance.get(Calendar.YEAR)));
                    toconver.setMonth(String.valueOf(instance.get(Calendar.MONTH)+1));
                    if(null == toconver){
                        list.add(materialCount);
                    }
                    materialCountMapper.insertMaterialCount(toconver);
                }else{
                    MaterialBom mb = new MaterialBom();
                    if(materialCount.getPartId().length()<6){
                        //其他
                        mb.setMateType("2");
                    }else{
                        String partId = materialCount.getPartId().substring(0, 6);
                        if (partId.equals("100202")){
                            //复合剂
                            mb.setMateType("0");
                        }else if(partId.equals("100201")){
                            //单剂
                            mb.setMateType("1");
                        }else{
                            //其他
                            mb.setMateType("2");
                        }
                    }
                    mb.setYear(String.valueOf(instance.get(Calendar.YEAR)));
                    mb.setMonth(String.valueOf(instance.get(Calendar.MONTH)+1));
                    mb.setPartId(materialCount.getPartId());
                    List<MaterialBom> materialBoms = materialBomMapper.selectMaterialBomList(mb);
                    if(materialBoms.isEmpty()){
                        mb.setId(IdGen.uuid());
                        mb.setChinaName(materialCount.getChinaName());
                        mb.setPeriodDate(materialCount.getPeriodDate());
                        mb.setErpName(materialCount.getErpName());
                        mb.setCounts(materialCount.getCounts());
                        mb.setUnit(materialCount.getUnit());
                        materialBomMapper.insertMaterialBom(mb);
                    }else{
                        MaterialBom materialBom = materialBoms.get(0);
                        materialBom.setCounts(materialBom.getCounts().add(materialCount.getCounts()));
                        materialBomMapper.updateMaterialBom(materialBom);
                    }
                    list.add(materialCount);
                }
            }else{
                MaterialCount materialCount1 = reslist.get(0);
                //存在 则累加
                String mateType = materialCount1.getMateType();
                BigDecimal counts = materialCount.getCounts();
                materialCount1.setProNum(materialCount1.getProNum().add(counts));
                materialCount1.setCounts(materialCount1.getCounts().add(counts));
                materialCount1.setTotalNum(materialCount1.getTotalNum().add(counts));
                int i = materialCountMapper.updateMaterialCount(materialCount1);
            }
        }
        //将未匹配的物料数据根据规格合并

        System.out.println("erp中未匹配到的物料个数-----------: "+list.size());
        //查询中间服务中所有root复合剂信息  根据复合剂信息生成单剂信息
        addmatec(instance);

    }

    /**
     * @author: Liujiaji
     * @date: 2024/4/3 08:36
     * @description: TODO 整理进料加工物料信息
     * @param: []
     * @return: java.lang.String
     **/
    public MaterialCount distggmeta(MaterialCount materialCount){
        //判断是否有进料加工
        String erpName = materialCount.getErpName();
        if(erpName.contains("进料加工")){
            //有则判断bom里面是否有数据
            BomData bc = new BomData();
            bc.setPartid(materialCount.getPartId());
            List<BomData> bomDatas = selectBomDataListOrder(bc);
            if(bomDatas.isEmpty()){
                //没有则查询同规格不带进料加工字段的物料
                List<MaterialCount> materialCounts = materialCountMapper.lookForMaterialbygg(materialCount);
                if(!materialCounts.isEmpty()){
                    MaterialCount materialCount1 = materialCounts.get(0);
                    BomData bc2 = new BomData();
                    bc2.setPartid(materialCount1.getPartId());
                    List<BomData> bomDatas2 = selectBomDataListOrder(bc2);
                    if(!bomDatas2.isEmpty()){
                        //查询bom是否为空
                        //不为空则返回信息
                        return materialCount1;
                    }
                }
            }
        }
        return null;
    }

    public void addmatec(Calendar instance){
        //1.根据时间查询所有的root
        MaterialCount pmater2 = new MaterialCount();
        pmater2.setYear(String.valueOf(instance.get(Calendar.YEAR)));
        pmater2.setMonth(String.valueOf(instance.get(Calendar.MONTH)+1));
        pmater2.setParentId("root");
        pmater2.setMateType("0");
        List<MaterialCount> materialCounts = materialCountMapper.selectMaterialCountList(pmater2);
        MaterialCount pmater = new MaterialCount();
        pmater.setYear(String.valueOf(instance.get(Calendar.YEAR)));
        pmater.setMonth(String.valueOf(instance.get(Calendar.MONTH)+1));
        //2.遍历root 获取root子集
        for (MaterialCount materialCount : materialCounts) {
            BomData bc = new BomData();
            bc.setBomname(materialCount.getBomName());
            bc.setBomvar(materialCount.getBomVar());
//            bc.setParentId(materialCount.getPartId());
//            bc.setAssembLelevel("2");

            //获取该父类下的所有子类
//            List<BomCost> bomCosts = bomCostMapper.selectBomCostList(bc);
            List<BomData> bomDatas =selectBomDataList(bc);
            for (BomData bomData : bomDatas) {
                //查询erp名字
                List<MaterialCount> materialCounts1 = materialCountMapper.lookForMate(bomData.getPartid());
                BigDecimal totalNum = materialCount.getTotalNum();
                BigDecimal total = totalNum.multiply(new BigDecimal(bomData.getCounts()));
                //先查询ma看看是否存在 存在则自增 不存在则 整理信息插入
                pmater.setParentId(bomData.getParentid());
                pmater.setPartId(bomData.getPartid());
                pmater.setPeriodDate(instance.getTime());
                String wldw = "";
                if(!materialCounts1.isEmpty()){
                    MaterialCount materialCount1 = materialCounts1.get(0);
                    pmater.setErpName(materialCount1.getErpName());
                    wldw = materialCount1.getUnit();
                }
                pmater.setChinaName(bomData.getChinaname());
                pmater.setAssembLelevel("2");
                //查询子信息有则插入 无则累计
                List<MaterialCount> materialCounts2 = materialCountMapper.selectMaterialCountList(pmater);
                if(materialCounts2.isEmpty()){
                    //如果为空则整理插入
                    pmater.setId(IdGen.uuid());
                    pmater.setType("2");
                    pmater.setBomVar(bomData.getBomvar());
                    pmater.setFatherPartvar(bomData.getFatherpartvar());
                    pmater.setAssembLelevel(bomData.getAssemblelevel());
                    pmater.setPartVar(bomData.getPartvar());
                    pmater.setTotalNum(total);
                    pmater.setNumType("0");
                    pmater.setParentId(bomData.getParentid());
                    pmater.setMateType("1");
                    pmater.setMateNum(total);
                    pmater.setCounts(total);
                    pmater.setSeqnum(bomData.getSeqnum());
                    pmater.setRootCode(bomData.getRootCode());
                    pmater.setBomName(bomData.getBomname());
                    pmater.setUnit(wldw);
                    materialCountMapper.insertMaterialCount(pmater);
                }else{
                    // 如果不为空则累计相加
                    MaterialCount materialCount1 = materialCounts2.get(0);
                    materialCount1.setTotalNum(materialCount1.getTotalNum().add(total));
                    materialCount1.setMateNum(materialCount1.getMateNum().add(total));
                    materialCount1.setCounts(materialCount1.getCounts().add(total));
                    materialCountMapper.updateMaterialCount(materialCount1);
                }

            }

        }

    }

    public MaterialCount toconver (BomData bomData,MaterialCount materialCount){
        //将bomCost 转换位materialCount
        //id为自动生成
        //数量不转换
        materialCount.setId(IdGen.uuid());
//        materialCount.setPartId(bomData.getPartid());
        materialCount.setBomVar(bomData.getBomvar());
        materialCount.setFatherPartvar(bomData.getFatherpartvar());
        materialCount.setAssembLelevel(bomData.getAssemblelevel());
        materialCount.setPartVar(bomData.getPartvar());
        materialCount.setTotalNum(materialCount.getCounts());
        materialCount.setNumType("1");
        materialCount.setProNum(materialCount.getCounts());
        materialCount.setBomVar(bomData.getBomvar());
        materialCount.setBomName(bomData.getBomname());
        if("root".equals(bomData.getParentid()) && "1".equals(bomData.getAssemblelevel())){
            //按照复合剂的标准存字段
            materialCount.setType("1");
            materialCount.setParentId("root");
            materialCount.setSeqnum(bomData.getSeqnum());
            materialCount.setRootCode(materialCount.getPartId());
        }else if(!"root".equals(bomData.getParentid()) && "2".equals(bomData.getAssemblelevel())){
            // 单剂标准字段
            materialCount.setType("2");
            materialCount.setParentId(bomData.getParentid());
        }else{
            materialCount.setType("2");
            materialCount.setParentId(bomData.getParentid());
        }
        return materialCount;
    }

    public List<BomData> selectBomDataListOrder(BomData pbd){
        List<BomData> bomDataList = new ArrayList<>();

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            DriverManager.setLoginTimeout(60);
            // 连接数据库
            conn = DriverManager.getConnection(sqlserverurl, sqlserverusername, sqlserverpassword);
            sql = conn.prepareStatement("select * from dbo.BOM_016 where  PARTID = ? and bomvar=(select max(BOMVAR) from dbo.BOM_016 where PARTID= ?)");
            sql.setString(1, pbd.getPartid());
            sql.setString(2, pbd.getPartid());

            //执行查询语句
            rs = sql.executeQuery();
            List<String> columns = new ArrayList<>();
            for (int i = 1; i < rs.getMetaData().getColumnCount(); i++) {
                columns.add(rs.getMetaData().getColumnName(i));
            }
            while (rs.next()) {
                BomData bomData = new BomData();
                bomData.setBomname(rs.getString("BOMNAME"));
                bomData.setBomvar(rs.getString("BOMVAR"));
                bomData.setSeqnum(rs.getString("SEQNUM"));
                bomData.setParentid(rs.getString("PARENTID"));
                bomData.setFatherpartvar(rs.getString("FATHERPARTVAR"));
                bomData.setPartid(rs.getString("PARTID"));
                bomData.setNodeExistMode(rs.getString("NODE_EXIST_MODE"));
                bomData.setAssemblelevel(rs.getString("ASSEMBLELEVEL"));
                bomData.setPartvar(rs.getString("PARTVAR"));
                bomData.setSupplystate(rs.getString("SUPPLYSTATE"));
                bomData.setCounts(rs.getString("COUNTS"));
                bomData.setAssembleproctime(rs.getString("ASSEMBLEPROCTIME"));
                bomData.setVirtualpart(rs.getString("VIRTUALPART"));
                bomData.setValidtime(rs.getString("VALIDTIME"));
                bomData.setInvalidtime(rs.getString("INVALIDTIME"));
                bomData.setIsreference(rs.getString("ISREFERENCE"));
                bomData.setOperation(rs.getString("OPERATION"));
                bomData.setFrezonbombackup2(rs.getString("FREZONBOMBACKUP2"));
                bomData.setFrezonbombackup3(rs.getString("FREZONBOMBACKUP3"));
                bomData.setChinaname(rs.getString("CHINANAME"));
                bomData.setTotalweight(rs.getString("TOTALWEIGHT"));
                bomData.setAutostat(rs.getString("AUTOSTAT"));
                bomData.setNodeExistMode1(rs.getString("NODE_EXIST_MODE"));
                bomData.setNodetimestamp(rs.getString("NODETIMESTAMP"));
                bomDataList.add(bomData);
            }
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (sql != null) {
                sql.close();
                sql = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bomDataList;
    }

    public List<BomData> selectBomDataList(BomData pbd){
        List<BomData> bomDataList = new ArrayList<>();

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            DriverManager.setLoginTimeout(60);
            // 连接数据库
            conn = DriverManager.getConnection(sqlserverurl, sqlserverusername, sqlserverpassword);
//               sql = conn.prepareStatement("select * from BOM_016 where 身份证号 = ? ORDER BY 报告日期 DESC");
            sql = conn.prepareStatement("select * from dbo.BOM_016 where  bomname = ? and bomvar = ? and ASSEMBLELEVEL= 2 ");

            sql.setString(1, pbd.getBomname());
            sql.setString(2, pbd.getBomvar());
//            sql.setString(2, pbd.getPartid());
            //执行查询语句
            rs = sql.executeQuery();
            List<String> columns = new ArrayList<>();
            for (int i = 1; i < rs.getMetaData().getColumnCount(); i++) {
                columns.add(rs.getMetaData().getColumnName(i));
            }
            while (rs.next()) {
                BomData bomData = new BomData();
                bomData.setBomname(rs.getString("BOMNAME"));
                bomData.setBomvar(rs.getString("BOMVAR"));
                bomData.setSeqnum(rs.getString("SEQNUM"));
                bomData.setParentid(rs.getString("PARENTID"));
                bomData.setFatherpartvar(rs.getString("FATHERPARTVAR"));
                bomData.setPartid(rs.getString("PARTID"));
                bomData.setNodeExistMode(rs.getString("NODE_EXIST_MODE"));
                bomData.setAssemblelevel(rs.getString("ASSEMBLELEVEL"));
                bomData.setPartvar(rs.getString("PARTVAR"));
                bomData.setSupplystate(rs.getString("SUPPLYSTATE"));
                bomData.setCounts(rs.getString("COUNTS"));
                bomData.setAssembleproctime(rs.getString("ASSEMBLEPROCTIME"));
                bomData.setVirtualpart(rs.getString("VIRTUALPART"));
                bomData.setValidtime(rs.getString("VALIDTIME"));
                bomData.setInvalidtime(rs.getString("INVALIDTIME"));
                bomData.setIsreference(rs.getString("ISREFERENCE"));
                bomData.setOperation(rs.getString("OPERATION"));
                bomData.setFrezonbombackup2(rs.getString("FREZONBOMBACKUP2"));
                bomData.setFrezonbombackup3(rs.getString("FREZONBOMBACKUP3"));
                bomData.setChinaname(rs.getString("CHINANAME"));
                bomData.setTotalweight(rs.getString("TOTALWEIGHT"));
                bomData.setAutostat(rs.getString("AUTOSTAT"));
                bomData.setNodeExistMode1(rs.getString("NODE_EXIST_MODE"));
                bomData.setNodetimestamp(rs.getString("NODETIMESTAMP"));
                bomDataList.add(bomData);
            }
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (sql != null) {
                sql.close();
                sql = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bomDataList;
    }

}
